﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace ResultsManagementSystem
{
    public class SubjectDao
    {
        private Subject subject;
        private SqlCeConnection conn;
        private SqlCeCommand cmd;
        private SqlCeDataReader rdr;

        public SubjectDao()
        {
            this.subject = new Subject();

            conn = new SqlCeConnection(@"Data Source=../../Results.sdf");
            conn.Open();

            cmd = conn.CreateCommand();
        }

        public SubjectDao(Subject subject)
        {
            this.subject = subject;

            conn = new SqlCeConnection(@"Data Source=../../Results.sdf");
            conn.Open();

            cmd = conn.CreateCommand();
        }

        ~SubjectDao()
        {
            conn.Close();
        }

        public Subject getSubject()
        {
            return this.subject;
        }

        public void setSubject(Subject subject)
        {
            this.subject = subject;
        }

        public Subject querrySubjectById(int id)
        {
            Subject subject = new Subject();

            cmd.CommandText = "SELECT * FROM Subject where id ='" + id + "'";

            rdr = cmd.ExecuteReader();

            if (rdr.Read())
            {
                subject.setId(rdr.GetInt32(0));
                subject.setName(rdr.GetString(1));
                subject.setTeacherId(rdr.GetInt32(2));
            }
            else
            {
                subject = null;
            }

            return subject;

        }

        //在课程表中查询一个课程
        public Subject querrySubjectByName(String name)
        {
            Subject subject = new Subject();

            cmd.CommandText = "SELECT * FROM Subject where name = '" + name + "'";
            
            rdr = cmd.ExecuteReader();

            if (rdr.Read())
            {
                subject.setId(rdr.GetInt32(0));
                subject.setName(rdr.GetString(1));
                subject.setTeacherId(rdr.GetInt32(2));
            }
            else
            {
                subject = null;
            }

            return subject;

        }

        //在课程表中查询所有课程
        public List<Subject> queryAllSubject()
        {
            List<Subject> subjectList = new List<Subject>();

            cmd.CommandText = "SELECT * FROM Subject";

            rdr = cmd.ExecuteReader();

            //得到所有课程
            while (rdr.Read())
            {
                subjectList.Add(new Subject(rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2)));
            }

            return subjectList;
        }

        public bool insertSubject()
        {
            Subject subjectTest = querrySubjectByName(this.subject.getName());
            
            //如果不重复，插入
            if (subjectTest != null)
            {
                return false;
            }
            else
            {
                //插入数据
                cmd.CommandText = "insert into Subject(name,teacherId) values('" + this.subject.getName() + "','" + this.subject.getTeacherId() + "')";
                cmd.ExecuteNonQuery();

                //增加成绩表
                //但不允许拆入符号，有待改进
                cmd.CommandText = "CREATE TABLE " + this.subject.getName() + "(id int PRIMARY KEY identity(1,1),number nvarchar(100),score int)";
                cmd.ExecuteNonQuery();

                return true;
            }
        }
    }
}
